
use master
GO
drop database WorkfloDev
go
create database WorkfloDev
GO
use WorkfloDev
GO

set xact_abort on
begin transaction

	create table [Language]
	(
		Id int,
		ISOName nvarchar(32),
		constraint PK_Language primary key nonclustered (Id)
	)

	create table CustomerStatus
	(
		Id int,
		Name nvarchar(64),
		Description nvarchar(256),
		constraint PK_CustomerStatus primary key nonclustered (Id)
	)

	create table Customer
	(
		Id int Identity(1,1),
		Name nvarchar(64) not null,
		CustomerStatusId int not null,
		constraint PK_Customer primary key nonclustered (Id),
		constraint FK_Customer_CustomerStatus foreign key (CustomerStatusId) references CustomerStatus (Id)
	)

	create table [Role]
	(
		Id int Identity(1,1),
		Rolename nvarchar(256) not null,
		ApplicationName nvarchar(256) not null,
		constraint PK_Role primary key nonclustered (Id)
	)

	create table [User]
	(
		Id int Identity(1,1),
		Username nvarchar(64) not null,
		DisplayName nvarchar(64) not null,
		Password nvarchar(256) not null,
		EmailAddress nvarchar(64) not null,
		IsApproved bit not null,
		ProvIderName nvarchar(64) not null,
		PasswordQuestion nvarchar(256) null,
		PasswordAnswer nvarchar(256) null,
		Comment nvarchar(512),
		IsLockedOut bit not null,
		CreationDate datetime not null,
		LastLoginDate datetime,
		LastActivityDate datetime,
		LastPasswordChangedDate datetime,
		LastLockoutDate datetime,
		FailedPasswordAttemptCount int not null default 0,
		FailedPasswordAttemptWindowStart datetime null,
		FailedPasswordAnswerAttemptCount int not null default 0,
		FailedPasswordAnswerAttemptWindowStart datetime null,
		ApplicationName nvarchar(256) not null,
		constraint PK_User primary key nonclustered (Id)
	)

	create table UserProfile
	(
		UserId int not null,
		LanguageId int not null,
		constraint PK_UserProfile primary key (UserId),
		constraint FK_UserProfile_User foreign key (UserId) references [User] (Id),
		constraint FK_UserProfile_Language foreign key (LanguageId) references [Language] (Id)
	)

	create table UserRole
	(
		UserId int not null,
		RoleId int not null,
		constraint PK_UserRole primary key nonclustered (UserId, RoleId),
		constraint FK_UserRole_User foreign key (UserId) references [User] (Id),
		constraint FK_UserRole_Role foreign key (RoleId) references [Role] (Id)
	)

	create table UserCustomer
	(
		UserId int not null,
		CustomerId int not null,
		constraint PK_UserCustomer primary key nonclustered (UserId, CustomerId),
		constraint FK_UserCustomer_User foreign key (UserId) references [User] (Id),
		constraint FK_UserCustomer_Customer foreign key (CustomerId) references Customer (Id)
	)

	create table ProjectStatus
	(
		Id int,
		Name nvarchar(64),
		Description nvarchar(256),
		constraint PK_ProjectStatus primary key nonclustered (Id)
	)

	create table Project
	(
		Id int Identity(1,1),
		ParentProjectId int,
		ProjectName nvarchar(64),
		Code nvarchar(64),
		CreatedByUserId int not null,
		ProjectStatusId int not null,
		constraint PK_Project primary key nonclustered (Id),
		constraint FK_Project_Project foreign key (ParentProjectId) references Project (Id),
		constraint FK_Project_ProjectStatus foreign key (ProjectStatusId) references ProjectStatus (Id),
		constraint FK_Project_User foreign key (CreatedByUserId) references [User] (Id)
	)

	create table ProjectCustomer
	(
		ProjectId int not null,
		CustomerId int not null,
		constraint PK_ProjectCustomer primary key nonclustered (ProjectId, CustomerId),
		constraint FK_ProjectCustomer_Project foreign key (ProjectId) references Project (Id),
		constraint FK_ProjectCustomer_Customer foreign key (CustomerId) references Customer (Id)
	)

	create table WorkflowState
	(
		Id int,
		Name nvarchar(64),
		Description nvarchar(256),
		EstimateApplicable bit not null,
		constraint PK_WorkflowState primary key nonclustered (Id)
	)

	CREATE TABLE WorkflowStateApplicableUser
	(
		Id INT Identity(1,1),
		WorkflowStateId int NOT NULL,
		UserId INT NOT NULL,
		CONSTRAINT PK_WorkflowStateApplicableUser PRIMARY KEY NONCLUSTERED (Id),
		CONSTRAINT FK_WorkflowStateApplicableUser_WorkflowState FOREIGN KEY (WorkflowStateId) REFERENCES WorkflowState (Id),
		CONSTRAINT FK_WorkflowStateApplicableUser_User FOREIGN KEY (UserId) REFERENCES [User] (Id)
	)

	create table WorkflowStateTransitionType
	(
		Id int not null,
		Name nvarchar(64),
		Description nvarchar(256),
		constraint PK_WorkflowStateTransitionType primary key nonclustered (Id)
	)

	CREATE table Workflow
	(
		Id int not null Identity(1,1),
		Name nvarchar(64),
		CreatedByUserId int not null,
		CONSTRAINT PK_Workflow PRIMARY KEY NONCLUSTERED (Id),
		constraint FK_User_Workflow foreign key (CreatedByUserId) references [User] (Id)
	)

	create table WorkflowStateTransition
	(
		Id int not null Identity(1,1),
		WorkflowId int not null,
		WorkflowStateTransitionTypeId int not null,
		FromWorkflowStateId int not null,
		ToWorkflowStateId int not null,
		constraint PK_WorkflowStateTransition primary key nonclustered (Id),
		CONSTRAINT FK_WorkflowStateTransition_Workflow FOREIGN KEY (WorkflowId) REFERENCES Workflow (Id),
		CONSTRAINT FK_WorkflowStateTransition_WorkflowStateTransitionType FOREIGN KEY (WorkflowStateTransitionTypeId) REFERENCES WorkflowStateTransitionType (Id),
		CONSTRAINT FK_WorkflowStateTransition_FromWorkflowState FOREIGN KEY (FromWorkflowStateId) REFERENCES WorkflowState (Id),
		CONSTRAINT FK_WorkflowStateTransition_ToWorkflowState FOREIGN KEY (ToWorkflowStateId) REFERENCES WorkflowState (Id)
	)

	create table WorkitemType
	(
		Id int not null,
		Name nvarchar(64),
		Description nvarchar(256),
		WorkflowId int not null,
		constraint PK_WorkitemType primary key nonclustered (Id),
		CONSTRAINT FK_WorkitemType_Workflow FOREIGN KEY (WorkflowId) REFERENCES Workflow (Id)
	)

	create table Workitem
	(
		Id bigint Identity(1,1),
		Title nvarchar(256),
		WorkitemTypeId int not null,
		Opened datetime not null default getdate(),
		Closed datetime,
		Description ntext,
		CreatedByUserId int not null,
		AssignedToUserId int not null,
		WorkflowStateId int not null,
		ProjectId int not null,
		constraint PK_Workitem primary key nonclustered (Id),
		CONSTRAINT FK_Workitem_WorkitemType FOREIGN KEY (WorkitemTypeId) REFERENCES WorkitemType (Id),
		CONSTRAINT FK_Workitem_CreatedByUser FOREIGN KEY (CreatedByUserId) REFERENCES [User] (Id),
		CONSTRAINT FK_Workitem_AssignedToUser FOREIGN KEY (AssignedToUserId) REFERENCES [User] (Id),
		CONSTRAINT FK_Workitem_WorkflowState FOREIGN KEY (WorkflowStateId) REFERENCES WorkflowState (Id),
		CONSTRAINT FK_Workitem_Project FOREIGN KEY (ProjectId) REFERENCES Project (Id)
	)

	create table WorkitemEstimate
	(
		WorkitemId bigint not null,
		WorkflowStateId int not null,
		InitialLowerHours int not null,
		InitialUpperHours int not null,
		CurrentLowerHours int not null,
		CurrentUpperHours int not null,
		constraint PK_WorkitemEstimate primary key nonclustered (WorkitemId, WorkflowStateId),
		CONSTRAINT FK_WorkitemEstimate_Workitem FOREIGN KEY (WorkitemId) REFERENCES Workitem (Id),
		CONSTRAINT FK_WorkitemEstimate_WorkflowState FOREIGN KEY (WorkflowStateId) REFERENCES WorkflowState (Id)
	)

	create table WorkitemHistoryType
	(
		Id int,
		Name nvarchar(64),
		Description nvarchar(256),
		constraint PK_WorkitemHistoryType primary key clustered (Id)
	)

	create table WorkitemHistory
	(
		Id bigint not null Identity(1,1),
		WorkitemId bigint not null,
		UserId int not null,
		WorkitemHistoryTypeId int not null,
		Added datetime not null default getdate(),
		ChangeSummary ntext,
		Info ntext,
		constraint PK_WorkitemHistory primary key (Id),
		CONSTRAINT FK_WorkitemHistory_Workitem FOREIGN KEY (WorkitemId) REFERENCES Workitem (Id),
		CONSTRAINT FK_WorkitemHistory_User FOREIGN KEY (UserId) REFERENCES [User] (Id),
		CONSTRAINT FK_WorkitemHistory_WorkitemHistoryType FOREIGN KEY (WorkitemHistoryTypeId) REFERENCES WorkitemHistoryType (Id)
	)
	
commit

CREATE USER [NETWORK SERVICE] FOR LOGIN [NT AUTHORITY\NETWORK SERVICE]
GO
EXEC sp_addrolemember N'db_datawriter', N'NETWORK SERVICE'
GO
EXEC sp_addrolemember N'db_datareader', N'NETWORK SERVICE'
GO